package com.company;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @description:
 * @author: ZhaoYicong
 * @date: Created in 2020/7/10 15:35
 * @version: v1.0
 * @modified By:
 */
public class StudentDAOImpl implements StudentDAO{
    public void insertdata(Connection connection, Student student) throws SQLException {
        String sql="insert into score value(null,?,?,?,?,?,?)";
        QueryRunner runner=new QueryRunner();
        runner.update(connection,sql,student.getStudentId(),student.getStudentName(),student.getChineseScore(),
                student.getEnglishScore(),student.getMathScore(),student.getTotalScore());

    }

    @Override
    public List<Student> searchall(Connection connection) throws SQLException  {
        String sql="select * from score";
        QueryRunner runner =new QueryRunner();
        BeanListHandler<Student> handler=new BeanListHandler<>(Student.class);
        List<Student> list = runner.query(connection, sql, handler);
        return list;
    }

    @Override
    public Student searchbyID(Connection connection, int id) throws SQLException {
      String sql="select * from score where id=?";
      QueryRunner runner =new QueryRunner();
        BeanHandler<Student> handler=new BeanHandler<>(Student.class);
        Student student = runner.query(connection, sql, handler, id);
        return student;
    }

    @Override
    public List<Student> searchbyName(Connection connection, String s) throws SQLException {
        String sql="select * from score where studentName=?";
        QueryRunner runner =new QueryRunner();
        BeanListHandler<Student> handler=new BeanListHandler<>(Student.class);
        List<Student> list = runner.query(connection, sql, handler, s);
        return list;
    }

    @Override
    public List<Student> searchbychineses(Connection connection, int cs) throws SQLException {
        String sql="select * from score where chineseScore=?";
        QueryRunner runner =new QueryRunner();
        BeanListHandler<Student> handler=new BeanListHandler<>(Student.class);
        List<Student> list = runner.query(connection, sql, handler, cs);
        return list;
    }

    @Override
    public List<Student> searchbymaths(Connection connection, int ms) throws SQLException {
        String sql="select * from score where mathScore=?";
        QueryRunner runner =new QueryRunner();
        BeanListHandler<Student> handler=new BeanListHandler<>(Student.class);
        List<Student> list = runner.query(connection, sql, handler, ms);
        return list;
    }

    @Override
    public List<Student> searchbyengliss(Connection connection, int es) throws SQLException {
        String sql="select * from score where englishScore=?";
        QueryRunner runner =new QueryRunner();
        BeanListHandler<Student> handler=new BeanListHandler<>(Student.class);
        List<Student> list = runner.query(connection, sql, handler, es);
        return list;
    }

    @Override
    public Student searchbySId(Connection connection, int sid) throws SQLException {
        String sql="select * from score where studentId=?";
        QueryRunner runner =new QueryRunner();
        BeanHandler<Student> handler=new BeanHandler<>(Student.class);
        Student student = runner.query(connection, sql, handler, sid);
        return student;
    }

    @Override
    public void updata(Connection connection, Student student) throws SQLException {
        QueryRunner runner =new QueryRunner();

        String sql="update score set studentId=?,studentName=?,chineseScore=?,englishScore=?," +
                "mathScore=?,totalScore=?, where id=?";

        runner.update(connection,sql,student.getStudentId(),student.getStudentName(),student.getChineseScore(),
                student.getEnglishScore(),student.getMathScore(),student.getTotalScore(),student.getId());
    }

    @Override
    public void deleteData(Connection connection, int id) throws SQLException {
        QueryRunner runner =new QueryRunner();
        String sql="delete from score where id=?";
        runner.update(connection,sql,id);
    }


}
